import 'dart:convert';

import 'package:drift/drift.dart';
import 'package:flutter/foundation.dart';
import 'package:get/get.dart';
import 'package:smart_translator/app/event/DataCallBack.dart';

import '../beans/buser_model.dart';
import '../beans/transcript_item_model.dart';
import '../const/AppConst.dart';
import '../services/DbService.dart';
import '../utils/CommonUtil.dart';
import 'database.dart';
import 'package:drift/drift.dart' as drift;

class DbDAOHelper {

  Future<UserData?> getLoginUserByToken (String accessToken) async {
    DbService service =  Get.find<DbService>();
    List<UserData> datas =  await (service.database!.select(service.database!.user)..where((u)=> u.access_token.equals(accessToken))).get();
    if (datas.isNotEmpty) {
      UserData udata = datas[0];
       return udata;
    } else {
       return null;
    }
  }



  void updateLoginUser(Buser userInfo) {
      DbService service =  Get.find<DbService>();
      (service.database!.select(service.database!.user)..where((u)=> u.username.equals(userInfo.username!))).get().then((u) {
        if (u.isNotEmpty) { //跟新一条数据.
          (service.database!.update(service.database!.user)..where((u)=> u.username.equals(userInfo.username!))).write(UserCompanion(
            email: drift.Value(userInfo.email??''),
            phone: drift.Value(userInfo.phone??''),
            username: drift.Value(userInfo.username!),
            header_icon: drift.Value(userInfo.avatar??''),
            access_token: drift.Value(userInfo.token!),
          ));

        } else {
          //新增一条数据.
          service.database!.into(service.database!.user).insert(UserCompanion.
          insert(email: userInfo.email??'', phone: userInfo.phone?? '', username: userInfo.username!, header_icon: userInfo.avatar??'',
              access_token: userInfo.token!, refresh_token: 'refresh_token', expired_time: 1111,
              create_time: DateTime.now(), update_time: DateTime.now()));


        }

    });
  }

  Future<int> getLoginUserId() async {
    UserData? userData =  await getLoginUserByToken(CommonUtil.getCurrentLoginToken());
    if (userData != null) {
      return userData.id;
    } else {
      return 0;
    }
  }


  void saveTranslateHistory (String src_lang,String tgt_lang,String raw_text,String translation,String taskId,int dataType,String fromLanDisplay, String toLanDisplay) async {
    int userId = await getLoginUserId();

    DbService service =  Get.find<DbService>();
    // 新增一条数据.
    service.database!.into(service.database!.translationHistory).insert(TranslationHistoryCompanion.
    insert(user_id: userId,src_lang: src_lang,tgt_lang: tgt_lang,raw_text: raw_text,translation: translation,taskId: taskId,dataType: dataType,
        create_time: DateTime.now(), update_time: DateTime.now(),src_lang_display: fromLanDisplay,tgt_lang_display: toLanDisplay));

  }

  Future<List<TranslationHistoryData>> obtainTranslateHistoryList(int dataType) async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.translationHistory)..where((u)=> u.user_id.equals(userId) & u.dataType.equals(dataType)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();
  }


  Future<Map<String,List<TranslationHistoryData>>> obtainTranslateHistoryListByGroup(int dataType) async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return (service.database!.select(service.database!.translationHistory)..where((u)=> u.user_id.equals(userId) & u.dataType.equals(dataType))).get().then(((item) {
      Map<String,List<TranslationHistoryData>> map = {};

      item.forEach((historyItem) {
        if (map[historyItem.taskId] == null) {
          map[historyItem.taskId] = [];
        }
        map[historyItem.taskId]!.add(historyItem);
      });

      return map;
    }));
  }


  Future<List<TranslationHistoryData>> obtainTranslateHistoryListByMode (String content,int dataType) async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return (service.database!.select(service.database!.translationHistory)..where((u) {
      return u.user_id.equals(userId) & (u.raw_text.like('%${content}%') | u.translation.like('%${content}%')) & u.dataType.equals(dataType);
    })..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.asc)
    ])).get();
  }


  Future<Setting?> obtainIndexSettings() async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    List<Setting> settings = await ((service.database!.select(service.database!.settings)..where((set)=> set.user_id.equals(userId)))).get();
    if (settings.isNotEmpty) {
      return settings[0];
    } else {
      return null;
    }
  }


 void saveOrUpdateIndexSetting(bool off_line,bool showTransText,bool allowOtherInput,bool autoPlay,int voiceType,int voiceSpeed) async {
    int userId = await getLoginUserId();

    DbService service =  Get.find<DbService>();
    (service.database!.select(service.database!.settings)..where((s)=> s.user_id.equals(userId))).get().then((settings) {
      if (settings.isNotEmpty) { //跟新一条数据.
        (service.database!.update(service.database!.settings)..where((s)=> s.user_id.equals(userId))).write(SettingsCompanion(
           off_line: drift.Value(off_line),
          only_show_trans_text: drift.Value(showTransText),
          allowOtherInput: drift.Value(allowOtherInput),
          auto_play: drift.Value(autoPlay),
          voiceType: drift.Value(voiceType),
          speechSpeed: drift.Value(voiceSpeed)
        ));

      } else {
        // 新增一条数据.
        service.database!.into(service.database!.settings).insert(SettingsCompanion.
        insert(user_id: userId,off_line:off_line,only_show_trans_text: showTransText,allowOtherInput: allowOtherInput,auto_play: autoPlay, voiceType: voiceType,
            speechSpeed: voiceSpeed,
            create_time: DateTime.now(), update_time: DateTime.now()));

      }

    });



  }


  void addFolder(String folderName) async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    service.database!.into(service.database!.folder).insert(FolderCompanion.
    insert(user_id: userId,f_name: folderName,
        create_time: DateTime.now(), update_time: DateTime.now()));
  }


  Future<List<FolderData>> obtainAllFolders() async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.folder)..where((f)=> f.user_id.equals(userId)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();

  }

  Future<List<FolderData>> obtainFolderByFolderId(int folderId) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.folder)..where((f)=> f.id.equals(folderId)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();

  }

  Future<List<FolderData>> obtainFolderByFolderName(String folderName) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.folder)..where((f)=> f.f_name.like(folderName)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();

  }


  Future<List<Session>> obtainAllSession() async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.sessions)..where((s)=> s.user_id.equals(userId) & s.folder_id.equals(AppConst.FOLDER_TYPE_RUBBISH).not()))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();
  }


  // folderId 0表示未分类. -1表示垃圾回收.
  Future<List<Session>> obtainSessionByFolderId(int folderId) async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.sessions)..where((s)=> s.user_id.equals(userId) & s.folder_id.equals(folderId)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();
  }


  Future<int> saveSession(int folderId,String audioPath,int lengthy) async {
    DbService service =  Get.find<DbService>();
    int userId = await getLoginUserId();
    return
         service.database!.into(service.database!.sessions).insert(SessionsCompanion.
         insert(user_id: userId,folder_id:folderId,session_name: DateTime.now().millisecondsSinceEpoch.toString(),s_name: 'NOTE',s_type: 'NOTE',has_read: false,
         audio_path: audioPath, audio_size:'0 M',audio_length: lengthy,
         create_time: DateTime.now(), update_time: DateTime.now()));
  }

  void updateSessionLengthyById(int sessionId,int lengthy) async {
    DbService service =  Get.find<DbService>();
    (service.database!.update(service.database!.sessions)..where((u)=> u.id.equals(sessionId))).write(SessionsCompanion(
      audio_length : drift.Value(lengthy),
    ));

  }

  void updateSessionHasReadById(int sessionId,bool hasRead) async {
    DbService service =  Get.find<DbService>();
    (service.database!.update(service.database!.sessions)..where((s)=> s.id.equals(sessionId))).write(SessionsCompanion(
      has_read : drift.Value(hasRead),
    ));

  }

  void updateSessionAudioPathById(int sessionId,String audioPath) async {
    DbService service =  Get.find<DbService>();
    (service.database!.update(service.database!.sessions)..where((s)=> s.id.equals(sessionId))).write(SessionsCompanion(
      audio_path : drift.Value(audioPath),
    ));

  }

  void saveRecordsTranscript(int sessionId,int chat_sequence,TranscriptItem transcriptItem, {DataCallBack? callback}) {
    DbService service =  Get.find<DbService>();
    //新增一条数据.
    service.database!.into(service.database!.audioTranscript).insert(AudioTranscriptCompanion.
    insert(session_id:sessionId, chat_sequence:chat_sequence, m_id:0, a_text:transcriptItem.text!,
        duration:transcriptItem.duration!, offset:transcriptItem.offset!, speaker: transcriptItem.speaker!,
        create_time: DateTime.now(), update_time: DateTime.now())).then((result) {
              if (callback != null) {
                callback.confirmCallBack!('');
              }
        });

  }


  Future<int> updateRecordsTranscript(int transcriptId, String scriptContent) async{
    DbService service =  Get.find<DbService>();
    //更新一条数据.
    return await (service.database!.update(service.database!.audioTranscript)..where((s)=> s.id.equals(transcriptId))).write(AudioTranscriptCompanion(
          a_text : drift.Value(scriptContent),
    ));

  }


  Future<List<Session>> obtainSessionBySessionId(int sessionId) async {
    int userId = await getLoginUserId();
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.sessions)..where((s)=> s.user_id.equals(userId) & s.id.equals(sessionId)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();
  }


  Future<List<AudioTranscriptData>> obtainTranscriptsBySessionId(int sessionId) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.audioTranscript)..where((s)=> s.session_id.equals(sessionId)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.asc)
    ])).get();
  }

  Future<List<AudioTranscriptData>> obtainTranscriptsBySessionIdAndSequenceId(int sessionId, int sequenceId) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.audioTranscript)..where((s)=> s.session_id.equals(sessionId) & s.chat_sequence.equals(sequenceId)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.asc)
    ])).get();
  }

  Future<List<AudioTranscriptData>> obtainTranscriptsBySessionIdAndContent(int sessionId, String content) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.audioTranscript)..where((s)=> s.session_id.equals(sessionId) & s.a_text.equals(content)))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.asc)
    ])).get();
  }


  Future<int> deleteTranscriptsById(int scriptId) async {
    DbService service =  Get.find<DbService>();
    int result = await (service.database!.delete(service.database!.audioTranscript)..where((s)=> s.id.equals(scriptId))).go();
    return result;
  }


  Future<void> saveSummary(int sessionId,String summaryContent) async {
    DbService service =  Get.find<DbService>();

    List<SummerizeData> summerizeDatas =
              await ((service.database!.select(service.database!.summerize)..where((s)=> s.session_id.equals(sessionId)))).get();
    if (summerizeDatas.isNotEmpty) {
       // 更新.
      (service.database!.update(service.database!.summerize)..where((s)=> s.session_id.equals(sessionId))).write(SummerizeCompanion(
          s_content: drift.Value(utf8.encode(summaryContent)),
          create_time: drift.Value(DateTime.now()),
          update_time: drift.Value(DateTime.now())
      ));

    } else {
      //新增一条数据.
      service.database!.into(service.database!.summerize).insert(SummerizeCompanion.
      insert(session_id:sessionId, m_id: 0, s_content: utf8.encode(summaryContent),
          create_time: DateTime.now(), update_time: DateTime.now()));
    }
  }

  Future<SummerizeData?> getSummaryBySessionId(String sessionId) async {
    DbService service =  Get.find<DbService>();
    List<SummerizeData> summerizeDatas =
    await ((service.database!.select(service.database!.summerize)..where((s)=> s.session_id.equals(int.parse(sessionId))))).get();

    if (summerizeDatas.isNotEmpty) {
       return summerizeDatas[0];
    } else {
      return null;
    }
  }

  void updateSessionNameBySessionId(int sessionId, String sessionName) {
    DbService service =  Get.find<DbService>();
    (service.database!.update(service.database!.sessions)..where((s)=> s.id.equals(sessionId))).write(SessionsCompanion(
        session_name: drift.Value(sessionName),
        update_time: drift.Value(DateTime.now())
    ));

  }

  void updateSessionFolderBySessionId(int sessionId, int folderId) {
    DbService service =  Get.find<DbService>();
    (service.database!.update(service.database!.sessions)..where((s)=> s.id.equals(sessionId))).write(SessionsCompanion(
        folder_id: drift.Value(folderId),
        update_time: drift.Value(DateTime.now())
    ));

  }

  // todo 需要处理总结内容的过滤.
 /* Future<List<SummerizeData>> obtainSummerizeDataByKeyWord(String keyword) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.summerize)..where((f)=>  f.s_content.like(folderName) ))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();

  }*/


  Future<List<AudioTranscriptData>> obtainAudioTranscriptByKeyWord(String keyword) async {
    DbService service =  Get.find<DbService>();
    return ((service.database!.select(service.database!.audioTranscript)..where((f)=> f.a_text.like('%'+keyword+'%')))..orderBy([
          (u) =>
          OrderingTerm(expression: u.create_time, mode: OrderingMode.desc)
    ])).get();

  }


  Future<int> deleteSessionByFolderId(int folderId) async {
    DbService service =  Get.find<DbService>();
    int result = await (service.database!.delete(service.database!.sessions)..where((s)=> s.folder_id.equals(folderId))).go();
    return result;
  }


  /*Stream<List<CategoryWithCount>> allCategoriesWithCount() {
    // select all categories and load how many associated entries there are for
    // each category
    DbService service =  Get.find<DbService>();
    return service.database!.customSelect(
      'SELECT *, (SELECT COUNT(*) FROM todos WHERE category = c.id) AS "amount"'
          ' FROM categories c;',
      // used for the stream: the stream will update when either table changes
      readsFrom: {todoItems, categories},
    ).watch().map((rows) {
      // we get list of rows here. We just have to turn the raw data from the
      // row into a CategoryWithCount instnace. As we defined the Category table
      // earlier, drift knows how to parse a category. The only thing left to do
      // manually is extracting the amount.
      return rows
          .map((row) => CategoryWithCount(
        category: categories.map(row.data),
        count: row.read<int>('amount'),
      ))
          .toList();
    });
  }*/

}

/*class CategoryWithCount {
  final Category category;
  final int count; // amount of entries in this category

  CategoryWithCount({required this.category, required this.count});
}*/

